package fussbudget.SQL;

import java.net.InetSocketAddress;
import java.net.Proxy;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.joda.money.Money;

public class SQLUtility {
	private Proxy				proxy				= new Proxy(Proxy.Type.SOCKS, new InetSocketAddress("proxyanbcge.nbc.com", 80));

	public static String		idIP;
	private static String		dbName				= "";
	private static String		dbNameArchive		= "";
	private static Connection	connect				= null;
	private static Connection	connectArchive		= null;
	private static Statement	statement			= null;
	private static ResultSet	resultSet			= null;

	public static Connection	activeConnection	= null;
	public static String		activeDBName		= "";

	public static boolean		isLocked			= false;

	public static void connectDataBase(String databaseName) throws ClassNotFoundException, SQLException {
		dbName = databaseName;
		// This will load the MySQL driver, each DB has its own driver
		Class.forName("com.mysql.jdbc.Driver");
		// Setup the connection with the DB
		// System.setProperty("socksProxyHost","proxyanbcge.nbc.com");
		// System.setProperty("socksProxyPort","80");
		connect = DriverManager.getConnection("jdbc:mysql://3.23.145.11/" + dbName + "?" + "user=vizrt&password=access");
		activeConnection = connect;
		activeDBName = dbName;
		System.out.println(connect.toString());
	}

	public static void disconnectDataBase() {
		close();
	}

	// You need to close the resultSet
	private static void close() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (activeConnection != null) {
				activeConnection.close();
			}
		} catch (Exception e) {

		}
	}

	public static void lock() {
		isLocked = true;
	}

	public static void unlock() {
		isLocked = false;
	}

	private static int executeQuery(String query) {
		int result = -1;
		if (!isLocked) {
			Statement statement = null;

			try {
				statement = activeConnection.createStatement();
				result = statement.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
				ResultSet genKeys = statement.getGeneratedKeys();
				int key = -1;
				while (genKeys.next()) {
					key = genKeys.getInt(1);
				}
				statement.close();
				return key;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	// ********************************************************************************************************
	// ***** PUT DATA INTO SQL *****
	// ********************************************************************************************************

	public static int addString(String tableName, String columnName, String data) {
		String data2 = data.replace("'", "\\'");
		String query = "INSERT INTO " + activeDBName + "." + tableName + " (" + columnName + ") VALUE('" + data2 + "')";
		int key = executeQuery(query);
		return key;
	}

	public static int addStrings(String tableName, String[] columnNames, String[] strings) {
		String column = "";
		for (int i = 0; i < columnNames.length - 1; i++) {
			column += columnNames[i] + ",";
		}
		column += columnNames[columnNames.length - 1];

		String data = "";
		for (int i = 0; i < strings.length - 1; i++) {
			String data2 = strings[i].replace("'", "\\'");
			data += data2 + ",";
		}
		data += strings[strings.length - 1];

		String query = "INSERT INTO " + activeDBName + "." + tableName + " (" + column + ") VALUES(" + data + ")";
		int key = executeQuery(query);
		return key;
	}

	public static int addInt(String tableName, String columnName, int newData) {
		String query = "INSERT INTO " + activeDBName + "." + tableName + " (" + columnName + ") VALUE(" + newData + ")";

		int key = executeQuery(query);
		return key;
	}

	public static int addInts(String tableName, Object[] objects, Object[] objects2) {
		String column = "";
		for (int i = 0; i < objects.length - 1; i++) {
			column += objects[i] + ",";
		}
		column += objects[objects.length - 1];

		String data = "";
		for (int i = 0; i < objects2.length - 1; i++) {
			data += objects2[i] + ",";
		}
		data += objects2[objects2.length - 1];

		String query = "INSERT INTO " + activeDBName + "." + tableName + " (" + column + ") VALUES(" + data + ")";
		int key = executeQuery(query);
		return key;
	}

	public static int addLong(String tableName, String columnName, long newData) {
		String query = "INSERT INTO " + activeDBName + "." + tableName + " (" + columnName + ") VALUE(" + newData + ")";
		int key = executeQuery(query);
		return key;
	}

	// ********************************************************************************************************
	// ***** ALTER DATA IN SQL *****
	// ********************************************************************************************************

	public static void alterString(String tableName, String columnName, String newData, Object whichRow) {
		if (whichRow instanceof String) {
			whichRow = "'" + whichRow + "'";
		}

		String pk = getPrimaryKey(tableName);
		String newData2 = newData.replace("'", "\\'");
		String newData3 = newData2.replace("\\", "\\\\\\\\");
		String query = "UPDATE " + activeDBName + "." + tableName + " SET `" + columnName + "`= '" + newData3 + "' WHERE " + pk + "=" + whichRow;
		int key = executeQuery(query);
	}

	public static void alterInt(String tableName, String columnName, int newData, Object whichRow) {
		if (whichRow instanceof String) {
			whichRow = "'" + whichRow + "'";
		}

		String pk = getPrimaryKey(tableName);
		String query = "UPDATE " + activeDBName + "." + tableName + " SET `" + columnName + "`= " + newData + " WHERE " + pk + "=" + whichRow;
		int key = executeQuery(query);
	}

	public static void alterLong(String tableName, String columnName, long newData, Object whichRow) {
		if (whichRow instanceof String) {
			whichRow = "'" + whichRow + "'";
		}

		String pk = getPrimaryKey(tableName);
		String query = "UPDATE " + activeDBName + "." + tableName + " SET `" + columnName + "`= " + newData + " WHERE " + pk + "=" + whichRow;
		int key = executeQuery(query);
	}

	public static void alterBoolean(String tableName, String columnName, boolean newData, Object whichRow) {
		if (whichRow instanceof String) {
			whichRow = "'" + whichRow + "'";
		}

		String pk = getPrimaryKey(tableName);
		String query = "UPDATE " + activeDBName + "." + tableName + " SET `" + columnName + "`= " + newData + " WHERE " + pk + "=" + whichRow;
		int key = executeQuery(query);
	}

	public static void alterDateTime(String tableName, String columnName, long newData, Object whichRow) {
		if (whichRow instanceof String) {
			whichRow = "'" + whichRow + "'";
		}

		String pk = getPrimaryKey(tableName);
		String query = "UPDATE " + activeDBName + "." + tableName + " SET `" + columnName + "`= " + newData + " WHERE " + pk + "=" + whichRow;
		int key = executeQuery(query);
	}

	public static void alterDecimal(String tableName, String columnName, Money amount, Object whichRow) {
		if (whichRow instanceof String) {
			whichRow = "'" + whichRow + "'";
		}

		String pk = getPrimaryKey(tableName);
		String query = "UPDATE " + activeDBName + "." + tableName + " SET `" + columnName + "`= " + amount.getAmount() + " WHERE " + pk + "=" + whichRow;
		int key = executeQuery(query);
	}

	// ********************************************************************************************************
	// ***** GET DATA FROM SQL *****
	// ********************************************************************************************************

	public static ResultSet getResultSet(String tableName, String columnName, String whereCondition) {
		Statement statement = null;
		ResultSet resultSet = null;
		String query;

		query = "SELECT " + columnName + " FROM " + tableName;

		if (!whereCondition.equals("")) {
			query += " WHERE " + whereCondition;
		}

		try {
			statement = activeConnection.createStatement();
			resultSet = statement.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSet;
	}

	public static ResultSet getResultSetFromActiveDB(String tableName, String columnName, String whereCondition) {
		Statement statement = null;
		ResultSet resultSet = null;
		String query;

		if (!isLocked) {
			query = "SELECT " + columnName + " FROM " + activeDBName + "." + tableName;

			if (!whereCondition.equals("")) {
				query += " WHERE " + whereCondition;
			}
			// Trace.info(query);

			try {
				statement = activeConnection.createStatement();
				resultSet = statement.executeQuery(query);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return resultSet;
	}

	// ********************************************************************************************************
	// ***** GET STRINGS FROM SQL *****
	// ********************************************************************************************************

	public static String[] getString(String[] tableNames, String columnName, String joinColumn) {
		String join = tableNames[0];
		if (tableNames.length > 1) {
			for (int i = 0; i < tableNames.length - 1; i++) {
				join += " INNER JOIN ";
			}
			join += tableNames[tableNames.length - 1] + " USING (" + joinColumn + ") ";
		}
		return getString(join, columnName, "");
	}

	public static String[] getString(String[] tableNames, String columnName, String joinColumn, String whereCondition) {
		String join = tableNames[0];
		if (tableNames.length > 1) {
			for (int i = 0; i < tableNames.length - 1; i++) {
				join += " INNER JOIN ";
			}
			join += tableNames[tableNames.length - 1] + " USING (" + joinColumn + ") ";
		}
		return getString(join, columnName, whereCondition);
	}

	public static String[] getString(String[] tableNames, String[] columnNames, String joinColumn) {
		String join = tableNames[0];
		if (tableNames.length > 1) {
			for (int i = 0; i < tableNames.length - 1; i++) {
				join += " INNER JOIN ";
			}
			join += tableNames[tableNames.length - 1] + " USING (" + joinColumn + ") ";
		}
		String columns = columnNames[0];
		if (columnNames.length > 1) {
			for (int i = 0; i < columnNames.length - 1; i++) {
				columns += ",";
			}
			columns += columnNames[columnNames.length - 1];
		}
		return getString(join, columns, "");
	}

	public static String[] getString(String tableName, String[] columnNames) {
		String columns = columnNames[0];
		if (columnNames.length > 1) {
			for (int i = 0; i < columnNames.length - 1; i++) {
				columns += ",";
			}
			columns += columnNames[columnNames.length - 1];
		}
		return getString(tableName, columns, "");
	}

	public static String[] getString(String tableName, String columnName) {
		return getString(tableName, columnName, "");
	}

	public static String[] getString(String tableName, String columnName, String whereCondition) {
		Statement statement = null;
		ResultSet resultSet = null;

		ArrayList<String> result = new ArrayList<String>();
		String[] result2 = { "" };
		if (!isLocked) {
			String query = "SELECT " + columnName + " FROM " + tableName;
			if (!whereCondition.equals("")) {
				query += " WHERE " + whereCondition;
			}

			try {
				statement = activeConnection.createStatement();
				resultSet = statement.executeQuery(query);
				ResultSetMetaData rsmd = resultSet.getMetaData();

				while (resultSet.next()) {
					for (int currCol = 1; currCol <= rsmd.getColumnCount(); currCol++) {
						result.add(resultSet.getString(currCol));
						// Trace.info(resultSet.getString(currCol));
					}
				}

				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			result2 = (String[]) result.toArray(result2);
		}
		return result2;
	}

	// ********************************************************************************************************
	// ***** REMOVE ITEM FROM SQL *****
	// ********************************************************************************************************

	public static void removeElement(String tableName, String whereCondition) {
		Statement statement = null;
		ResultSet resultSet = null;

		if (!isLocked) {
			String cmd = "DELETE FROM " + activeDBName + "." + tableName + " WHERE " + whereCondition;
			// Trace.info(cmd);

			try {
				statement = activeConnection.createStatement();
				int arg = statement.executeUpdate(cmd);
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// ********************************************************************************************************
	// ***** GET INTS FROM SQL *****
	// ********************************************************************************************************

	public static Integer[] getInt(String[] tableNames, String columnName, String joinColumn) {
		String join = tableNames[0];
		if (tableNames.length > 2) {
			for (int i = 0; i < tableNames.length - 1; i++) {
				join += " INNER JOIN ";
			}
			join += tableNames[tableNames.length - 1] + " USING (" + joinColumn + ") ";
		}
		return getInt(join, columnName, "");
	}

	public static Integer[] getInt(String[] tableNames, String columnName, String joinColumn, String whereCondition) {
		String join = tableNames[0];
		if (tableNames.length > 1) {
			for (int i = 0; i < tableNames.length - 1; i++) {
				join += " INNER JOIN ";
			}
			join += tableNames[tableNames.length - 1] + " USING (" + joinColumn + ") ";
		}
		return getInt(join, columnName, whereCondition);
	}

	public static Integer[] getInt(String tableName, String columnName) {
		return getInt(tableName, columnName, "");
	}

	public static Integer[] getInt(String tableName, String columnName, String whereCondition) {
		Statement statement = null;
		ResultSet resultSet = null;

		ArrayList<Integer> result = new ArrayList<Integer>();
		Integer[] result2 = { 0 };
		String query = "SELECT " + columnName + " FROM " + activeDBName + "." + tableName;
		if (!whereCondition.equals("")) {
			query += " WHERE " + whereCondition;
		}

		try {
			statement = activeConnection.createStatement();
			resultSet = statement.executeQuery(query);
			resultSet.last();
			if (resultSet.getRow() > 0) {
				resultSet.first();
				ResultSetMetaData rsmd = resultSet.getMetaData();
				do {
					for (int currCol = 1; currCol <= rsmd.getColumnCount(); currCol++) {
						result.add(resultSet.getInt(currCol));
					}
				} while (resultSet.next());

				statement.close();
				result2 = (Integer[]) result.toArray(result2);
				return result2;
			} else {
				return null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	// ********************************************************************************************************
	// ***** GET LONGS FROM SQL -- dates for example *****
	// ********************************************************************************************************

	public static Long[] getLong(String[] tableNames, String columnName, String joinColumn) {
		String join = tableNames[0];
		if (tableNames.length > 2) {
			for (int i = 0; i < tableNames.length - 1; i++) {
				join += " INNER JOIN ";
			}
			join += tableNames[tableNames.length - 1] + " USING (" + joinColumn + ") ";
		}
		return getLong(join, columnName, "");
	}

	public static Long[] getLong(String[] tableNames, String columnName, String joinColumn, String whereCondition) {
		String join = tableNames[0];
		if (tableNames.length > 1) {
			for (int i = 0; i < tableNames.length - 1; i++) {
				join += " INNER JOIN ";
			}
			join += tableNames[tableNames.length - 1] + " USING (" + joinColumn + ") ";
		}
		return getLong(join, columnName, whereCondition);
	}

	public static Long[] getLong(String tableName, String columnName) {
		return getLong(tableName, columnName, "");
	}

	public static Long[] getLong(String tableName, String columnName, String whereCondition) {
		Statement statement = null;
		ResultSet resultSet = null;

		ArrayList<Integer> result = new ArrayList<Integer>();
		Long[] result2 = { 0L };
		String query = "SELECT " + columnName + " FROM " + tableName;
		if (!whereCondition.equals("")) {
			query += " WHERE " + whereCondition;
		}

		try {
			statement = activeConnection.createStatement();
			resultSet = statement.executeQuery(query);
			resultSet.last();
			if (resultSet.getRow() > 0) {
				resultSet.first();
				ResultSetMetaData rsmd = resultSet.getMetaData();
				while (resultSet.next()) {
					for (int currCol = 1; currCol <= rsmd.getColumnCount(); currCol++) {
						result.add(resultSet.getInt(currCol));
					}
				}

				statement.close();
				result2 = (Long[]) result.toArray(result2);
				return result2;
			} else {
				return null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	// ********************************************************************************************************
	// ***** OTHER UTILITY *****
	// ********************************************************************************************************

	public static String getPrimaryKey(String tableName) {
		Statement statement = null;
		ResultSet resultSet = null;

		String pk = "";
		String queryPK = "SHOW KEYS FROM " + activeDBName + "." + tableName + " WHERE Key_name = 'PRIMARY'";

		try {
			statement = activeConnection.createStatement();
			resultSet = statement.executeQuery(queryPK);
			resultSet.next();
			pk = resultSet.getString("Column_name");
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pk;
	}

	public static int getNextAvailableID(String tableName) {
		Statement statement = null;
		ResultSet resultSet = null;

		int nextID = -1;
		String pk = getPrimaryKey(tableName);
		String queryMax = "SELECT MAX(" + pk + ") AS max FROM " + tableName;

		try {
			statement = activeConnection.createStatement();
			resultSet = statement.executeQuery(queryMax);
			resultSet.next();
			nextID = resultSet.getInt("max") + 1;
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return nextID;
	}

	public static int getColumnCount(String tableName) {
		Statement statement = null;
		ResultSet resultSet = null;

		int numColumns = 0;
		String query = "DESCRIBE " + tableName;

		try {
			statement = activeConnection.createStatement();
			resultSet = statement.executeQuery(query);

			while (resultSet.next()) {
				numColumns++;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return numColumns;
	}
}
